<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<!-- Purpose: XSLT to convert SQL schema XML into DROP/CREATE of DB objects.
	 Known limitations: no support for indexed views or the index fillfactor. 
	 Author: Lindsey Lewis, 08-26-2005 -->
<xsl:output method="xml" omit-xml-declaration="yes"/>

	<xsl:template match="/">
		<xsl:apply-templates/>
	</xsl:template>

<!-- template to output ALL SQL statement types -->
	<xsl:template match="DataBase_Schema">
		<xsl:apply-templates select="Database"/>
<!-- generate defaults, rules and UDDTs: in that order -->
		<xsl:apply-templates select="DEFAULT"/>
		<xsl:apply-templates select="RULE"/>
		<xsl:apply-templates select="UDDT"/>
<!-- templates to drop all FK old and new constraints -->
		<xsl:apply-templates select="TABLE/TABLE_ORIG_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]"/>
		<xsl:apply-templates select="TABLE/TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="DROP"/>
		<xsl:apply-templates select="TABLE"/>
<!-- template so that the FK constraints would get created after all the tables were -->
		<xsl:apply-templates select="TABLE/TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="ADD"/>
	    <xsl:apply-templates select="VIEW"/>
	    <xsl:apply-templates select="FUNC"/>
	    <xsl:apply-templates select="SPROC"/>
	    <xsl:apply-templates select="TRIGGER"/>
	    
DECLARE @ERR int

SET @ERR = @@ERROR
IF @@TRANCOUNT > 0
BEGIN
	IF @ERR > 0
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION T1
END
SET XACT_ABORT OFF
	</xsl:template>
	
<!-- template to output comment header -->
	<xsl:template match="Database">
-- DB Name: <xsl:value-of select="Name"/>
-- Output Date: <xsl:value-of select="Date"/>
-- Output Time: <xsl:value-of select="Time"/>
-- AutoGenerated SQL: using the SQL Schema Tool.

/*
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time 
error, the entire transaction is terminated and rolled back. When OFF, only the 
Transact-SQL statement that raised the error is rolled back and the transaction 
continues processing. Compile errors, such as syntax errors, are not affected by 
SET XACT_ABORT.
*/

SET QUOTED_IDENTIFIER ON
SET XACT_ABORT ON
BEGIN TRANSACTION T1 WITH MARK 'Apply Diffgram SQL'
	</xsl:template>

<!-- template to output defaults -->
	<xsl:template match="DEFAULT">
<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="DEFAULT_NAME"/>]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
BEGIN
	DROP DEFAULT [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="DEFAULT_NAME"/>]
END
GO
-- add new default
<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="DEFAULT_TEXT"/></xsl:call-template>
GO
</xsl:if>
	</xsl:template>

<!-- template to output rules -->
	<xsl:template match="RULE">
<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="RULE_NAME"/>]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
	DROP RULE [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="RULE_NAME"/>]
END
GO
-- add new rule
<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="RULE_TEXT"/></xsl:call-template>
GO
</xsl:if>
	</xsl:template>

<!-- template to output UDDTs -->
	<xsl:template match="UDDT">
<xsl:if test="@Action='Add'">
if exists (select * from dbo.systypes where name = N'<xsl:value-of select="UDDT_NAME"/>')
BEGIN
	EXEC sp_droptype N'<xsl:value-of select="UDDT_NAME"/>'
END
GO
	
<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="UDDT_TEXT"/></xsl:call-template>
GO
</xsl:if>
	</xsl:template>
	
<!-- template to output Create Table statement -->
	<xsl:template match="TABLE">
		<xsl:variable name="_towner"><xsl:value-of select="TABLE_OWNER"/></xsl:variable> 
<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
END
GO
	
IF (SELECT FILEGROUP_ID('<xsl:value-of select="TABLE_FILEGROUP/groupname"/>')) IS NOT NULL
	CREATE TABLE [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	(   <xsl:for-each select="COLUMN[isComputed=0]"><xsl:call-template name="_CreateColumn"/></xsl:for-each>
	)   <xsl:apply-templates select="TABLE_FILEGROUP"/> <xsl:choose>
		<xsl:when test="COLUMN[Base_Type='image']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> <xsl:when test="COLUMN[Base_Type='ntext']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> <xsl:when test="COLUMN[Base_Type='text']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> </xsl:choose>
ELSE
	RAISERROR ('You will have to manually add the filegroup <xsl:value-of select="TABLE_FILEGROUP/groupname"/> to the SQL DB or edit this SQL script to set the FileGroups to PRIMARY', 16, 1)
RETURN
GO

</xsl:if>
<xsl:if test="@Action='Alter'">
<!-- Drop all original constraints for table, indexes, and unbind original column rules -->
	<xsl:for-each select="COLUMN[string-length(Rule_Orig_Name)>0]">
		<xsl:call-template name="_unbindColumnRule"/>
	</xsl:for-each>
		
	<xsl:for-each select="COLUMN[Action='Drop']">
		<xsl:call-template name="_DropUserDefaults"/>
	</xsl:for-each>

	<xsl:for-each select="COLUMN[Action='Alter']">
		<xsl:call-template name="_DropUserDefaults"/>
	</xsl:for-each>

	<xsl:apply-templates select="TABLE_ORIG_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]"/>
	<xsl:apply-templates select="TABLE_ORIG_INDEX[string-length(index_name)>0]"/>
	<xsl:apply-templates select="DropAdd_References[string-length(Constraint)>0]" mode="Drop"/>
	<xsl:apply-templates select="DropAdd_Indexes[string-length(index_name)>0]" mode="Drop"/>

<xsl:if test="count(COLUMN) > 0">
-- COUNT OF ALTERED COLUMNS IN TABLE [<xsl:value-of select="TABLE_NAME"/>]: <xsl:value-of select="count(COLUMN)"/>
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	<xsl:for-each select="COLUMN[Action='Drop']">
		<xsl:if test="position()=1">
	-- drop existing table columns that don't exist in new schema</xsl:if>
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>]
	<xsl:call-template name="_Column"><xsl:with-param name="_action" select="./Action"/></xsl:call-template>
<xsl:text>
		</xsl:text>	
	</xsl:for-each>
	<xsl:for-each select="COLUMN[Action='Add' and isComputed=0]">
		<xsl:if test="position()=1">
	-- add new table columns
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>] 
	ADD </xsl:if>
		<xsl:call-template name="_Column"><xsl:with-param name="_action" select="./Action"/></xsl:call-template><xsl:if test="position()!=last()">,
		</xsl:if>
<xsl:if test="position()=last()"><xsl:text>
		</xsl:text></xsl:if>
	</xsl:for-each>
	<xsl:for-each select="COLUMN[Action='Add' and isComputed=0 and isRowGuidCol=0 and not(contains(Base_Type,'text')) and ((isNullable=0 and string-length(Default_Name)=0) or (string-length(Collation)>0 and Type!=Base_Type))]">
		<xsl:if test="position()=1">
--2nd pass thru after 'Add' table columns to handle those columns having Not Nulls </xsl:if>
		<xsl:call-template name="_Column2ndPass"/>
	</xsl:for-each>
  <xsl:for-each select="COLUMN[Action='Alter' and contains(./Type,'text')]">
    print 'WARNING: The text field ''<xsl:value-of select="Column_Name"/>'' has changed, most likely due to collation. SQL does not allow the Alter of a text field.'
  </xsl:for-each>
  <xsl:for-each select="COLUMN[Action='Alter' and not(contains(./Type,'text'))]">
		<xsl:if test="position()=1">
	-- Alter existing table columns </xsl:if>
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>]
		<xsl:call-template name="_Column"><xsl:with-param name="_action" select="./Action"/></xsl:call-template>
<xsl:text>
		</xsl:text>		
	</xsl:for-each>
	<xsl:for-each select="COLUMN[Action='Alter' and isComputed=0 and isRowGuidCol=0 and not(contains(Base_Type,'text')) and ((string-length(Collation)>0 and Type!=Base_Type) or (isNullable=0 and string-length(Default_Name)=0))]">
		<xsl:if test="position()=1">
--2nd pass thru after 'Alter' table columns to handle those columns having UDDTs </xsl:if>
		<xsl:call-template name="_Column2ndPass"/>
	</xsl:for-each>
	<xsl:for-each select="COLUMN[Action='Alter' and string-length(Default_Name)>0]">
<xsl:if test="position()=1">
-- Adds default values for user defined defaults on 'Altered' columns
</xsl:if>
		<xsl:call-template name="_ColumnUserDefaults"/>
	</xsl:for-each>
	<xsl:for-each select="COLUMN[Action='Alter']">
		<xsl:call-template name="_ColumnROWGUIDCOL"/>
	</xsl:for-each>
	
END
ELSE
	RAISERROR ('Error applying table changes: <xsl:value-of select="TABLE_NAME"/> does not exist in catalog!', 16, 1)
GO
</xsl:if></xsl:if>
<!-- apply templates to output new calculated columns -->
		<xsl:for-each select="COLUMN[isComputed=1]">
			<xsl:call-template name="_CalcColumn"/>
			<xsl:if test="position()=last()"><xsl:text>
GO

</xsl:text>
			</xsl:if>	
		</xsl:for-each>
		<xsl:for-each select="COLUMN[string-length(Rule_Name)>0]">
			<xsl:call-template name="_bindColumnRule"/>
		</xsl:for-each>

<!-- apply templates to output new constraints and indexes -->
		<xsl:apply-templates select="TABLE_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]"/>
		<xsl:apply-templates select="TABLE_INDEX[string-length(index_name)>0]" mode="PK"/>
		<xsl:apply-templates select="TABLE_INDEX[string-length(index_name)>0]" mode="NDX"/>
		<xsl:apply-templates select="DropAdd_Indexes[string-length(index_name)>0]" mode="Add"/>
		<xsl:apply-templates select="DropAdd_References[string-length(Constraint)>0]" mode="Add"/>
	</xsl:template>
	
<!-- template to output non-calculated CREATE TABLE Column -->
	<xsl:template name="_CreateColumn" match="COLUMN[isComputed=0]">
	    <xsl:variable name="_isIdent" select="isIdentity"/>
	    <xsl:variable name="_type" select="Base_Type"/>
		[<xsl:value-of select="Column_Name"/>] [<xsl:value-of select="$_type"/>] <xsl:if test="contains($_type, 'char')"> (<xsl:value-of select="Length"/>) </xsl:if> <xsl:if test="$_isIdent=1"> Identity (<xsl:value-of select="Seed"/> ,<xsl:value-of select="Increment"/>)</xsl:if><xsl:if test="NotforRepl=1"> NOT FOR REPLICATION</xsl:if><xsl:if test="string-length(Collation)>0"> COLLATE </xsl:if> <xsl:value-of select="Collation"/><xsl:if test="isNullable=0"> NOT</xsl:if> NULL<xsl:if test="isRowGuidCol=1"> ROWGUIDCOL</xsl:if><xsl:if test="string-length(Default_Name)>0"> DEFAULT <xsl:value-of select="Default_Value"/></xsl:if><xsl:if test="position()!=last()">,</xsl:if>
	</xsl:template>
	
<!-- template to output calculated Table Column -->
	<xsl:template name="_CalcColumn" match="COLUMN[isComputed=1]">
	<xsl:if test="position()=1">
	-- add calculated columns to tables	
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	ADD </xsl:if>[<xsl:value-of select="Column_Name"/>] AS <xsl:value-of select="Calc_Text"/><xsl:if test="position()!=last()">,
	</xsl:if>
	</xsl:template>
	
<!-- template to output Table Column if table is being altered -->
	<xsl:template name="_Column" match="COLUMN">
		<xsl:param name="_action"/>
	    <xsl:variable name="_isIdent" select="isIdentity"/>
	    <xsl:variable name="_btype" select="Base_Type"/>
	    <xsl:variable name="_type" select="Type"/>
      <xsl:if test="$_action='Add'"> [<xsl:value-of select="Column_Name"/>] </xsl:if><xsl:if test="$_action='Alter' and not(contains($_type,'text'))">ALTER COLUMN  [<xsl:value-of select="Column_Name"/>] </xsl:if><xsl:if test="$_action='Drop'">DROP COLUMN  [<xsl:value-of select="Column_Name"/>] </xsl:if><xsl:if test="$_action!='Drop' and not(contains($_type,'text'))"><xsl:if test="string-length(Collation)>0"> [<xsl:value-of select="$_btype"/>] </xsl:if><xsl:if test="string-length(Collation)=0"> [<xsl:value-of select="$_type"/>] </xsl:if><xsl:if test="string-length(Collation)>0 and contains($_btype,'char')"> (<xsl:value-of select="Length"/>) </xsl:if><xsl:if test="$_isIdent=1"> Identity (<xsl:value-of select="Seed"/> ,<xsl:value-of select="Increment"/>) </xsl:if><xsl:if test="string-length(Collation)>0"> COLLATE </xsl:if><xsl:value-of select="Collation"/><xsl:if test="(string-length(Default_Name)>0 and isNullable=0) or ($_action='Alter' and isNullable=0)"> NOT NULL </xsl:if><xsl:if test="(string-length(Default_Name)=0 and $_action='Add') or isNullable=1"> NULL </xsl:if><xsl:if test="string-length(Default_Name)>0 and $_action='Add'"> DEFAULT <xsl:value-of select="Default_Value"/> </xsl:if></xsl:if>
	</xsl:template> 

<!-- template to fix Table Column that are Not Null and does not have a default value, or for UDDTs for the ADDed/Altered columns -->
	<xsl:template name="_Column2ndPass">
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	ALTER COLUMN [<xsl:value-of select="Column_Name"/>] [<xsl:value-of select="Type"/>] <xsl:if test="contains(Base_Type,'char') and Base_Type=Type"> (<xsl:value-of select="Length"/>) </xsl:if><xsl:if test="isNullable=0">NOT NULL</xsl:if><xsl:text>
</xsl:text> 
	</xsl:template>		
	
<!-- template to add user defined defaults as constraints to table -->
	<xsl:template name="_ColumnUserDefaults" match="COLUMN[Action='Alter' and string-length(Default_Name)>0]">
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>] 
	ADD DEFAULT <xsl:value-of disable-output-escaping="yes" select="Default_Value"/> FOR [<xsl:value-of select="Column_Name"/>]
	</xsl:template>

<!-- template to add ROWGUIDCOL to table column -->
	<xsl:template name="_ColumnROWGUIDCOL" match="COLUMN[Action='Alter']">
		<xsl:if test="isRowGuidCol=1 and ORIG_RowGuidCol=0">
-- Adds ROWGUIDCOL setting to column
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>] 
	ALTER COLUMN [<xsl:value-of select="Column_Name"/>] ADD ROWGUIDCOL 
		</xsl:if>
		<xsl:if test="isRowGuidCol=0 and ORIG_RowGuidCol=1">
-- Drops ROWGUIDCOL setting to column
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>] 
	ALTER COLUMN [<xsl:value-of select="Column_Name"/>] DROP ROWGUIDCOL 
		</xsl:if>
	</xsl:template>

<!-- template to drop existing user defined defaults attached to table columns -->
	<xsl:template name="_DropUserDefaults">
<!--xsl:if test="string-length(Default_Value)>0 and Default_Name=Default_Orig_Name">
IF (SELECT OBJECT_ID(N'<xsl:value-of select="Default_Name"/>')) IS NOT NULL
	BEGIN
		EXEC sp_unbindefault '[<xsl:value-of select="TABLE_NAME"/>].[<xsl:value-of select="Column_Name"/>]'
	END
	ELSE
		PRINT 'User defined default <xsl:value-of select="Default_Name"/> does not exist for column: <xsl:value-of select="TABLE_NAME"/>.<xsl:value-of select="Column_Name"/>, no need to perform unbind.'
GO</xsl:if-->
<xsl:if test="string-length(Default_Orig_Name)>0"> <!--  and Default_Name!=DEFAULT_ORIG_NAME -->
-- unbind original user defined defaults from the column as it may have changed and will be re-added as a standard default constraint
IF (SELECT OBJECT_ID(N'<xsl:value-of select="Default_Orig_Name"/>')) IS NOT NULL
BEGIN
	EXEC sp_unbindefault '<xsl:value-of select="TABLE_NAME"/>.<xsl:value-of select="Column_Name"/>'
END
ELSE
	PRINT 'User defined default <xsl:value-of select="Default_Orig_Name"/> does not exist for column: <xsl:value-of select="TABLE_NAME"/>.<xsl:value-of select="Column_Name"/>, no need to perform unbind.'
GO</xsl:if>
	</xsl:template>

<!-- template to bind new rule to Table Column - note that is the new rule and the old rule are the same then nothing will be output -->
	<xsl:template name="_bindColumnRule" match="COLUMN[string-length(Rule_Name)>0]">
		<xsl:if test="(string-length(Rule_Orig_Name)>0 and Rule_Name!=Rule_Orig_Name) or string-length(Rule_Orig_Name)=0">
			<xsl:if test="position()=1">
-- Bind rule to column
			</xsl:if>
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="Rule_Owner"/>].[<xsl:value-of select="Rule_Name"/>]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
	EXEC sp_bindrule <xsl:value-of select="Rule_Name"/>, '[<xsl:value-of select="TABLE_NAME"/>].[<xsl:value-of select="Column_Name"/>]'
END
ELSE
	RAISERROR ('Cannot bind rule to column: [<xsl:value-of select="Column_Name"/>].  Rule: <xsl:value-of select="Rule_Name"/> does not exist.', 16, 1)
GO
		</xsl:if>
	</xsl:template>

<!-- template to unbind original rule from Table Column - note that is the new rule and the old rule are the same then nothing will be output -->
	<xsl:template name="_unbindColumnRule" match="COLUMN[string-length(Rule_Orig_Name)>0]">
		<xsl:if test="(string-length(Rule_Name)>0 and Rule_Name!=Rule_Orig_Name) or string-length(Rule_Name)=0">
			<xsl:if test="position()=1">
-- UnBind original rule from column
			</xsl:if>
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="Rule_Orig_Owner"/>].[<xsl:value-of select="Rule_Orig_Name"/>]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
	EXEC sp_unbindrule '[<xsl:value-of select="TABLE_NAME"/>].[<xsl:value-of select="Column_Name"/>]'
END
ELSE
	PRINT 'No need to unbind rule from column: [<xsl:value-of select="Column_Name"/>].  Rule: <xsl:value-of select="Rule_Orig_Name"/> does not exist.'
GO
		</xsl:if>
	</xsl:template>

<!-- template to output Table Filegroup -->
	<xsl:template match="TABLE_FILEGROUP">ON [<xsl:value-of select="groupname"/>] </xsl:template>

<!-- template calls template to output Index Table Constraints statements -->
	<xsl:template match="TABLE_INDEX[string-length(index_name)>0]" mode="PK">
		<xsl:call-template name="PK_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
	</xsl:template>

<!-- template calls template for the drop Index Table statements which will get re-added later -->
	<xsl:template match="DropAdd_Indexes[string-length(index_name)>0]" mode="Drop">
		<xsl:if test="@Action='Drop' or @Action='ReAdd'"><xsl:variable name="_idxName" select="index_name"/>
		<xsl:variable name="_idxOrigName" select="../TABLE_ORIG_INDEX[index_name=$_idxName]"/>
		<!--xsl:variable name="_idxNewName" select="../TABLE_INDEX/index_name"/-->
		<xsl:if test="string-length($_idxOrigName)=0">
		
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- drop existing indexes to re-add later to prevent issues with dependant columns
		<xsl:call-template name="DROP_INDEX">
			<xsl:with-param name="_comment">no</xsl:with-param>
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
END
GO
		</xsl:if></xsl:if>
	</xsl:template>

<!-- template calls template for the add Index Table statements that were to be re-added later -->
	<xsl:template match="DropAdd_Indexes[string-length(index_name)>0]" mode="Add">
		<xsl:if test="@Action='ReAdd'"><xsl:variable name="_idxName" select="index_name"/>
		<!--xsl:variable name="_idxOrigName" select="../TABLE_ORIG_INDEX[index_name=$_idxName]"/-->
		<xsl:variable name="_idxNewName" select="../TABLE_INDEX[index_name=$_idxName]"/>
		<xsl:if test="string-length($_idxNewName)=0">
		
-- re-add dropped existing indexes previously dropped to prevent issues with dependant columns		
		<xsl:call-template name="PK_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
		<xsl:call-template name="T_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
		</xsl:if></xsl:if>
	</xsl:template>

<!-- template calls template for the drop References Table statements which will get re-added later -->
	<xsl:template match="DropAdd_References[string-length(Constraint)>0]" mode="Drop">
	<xsl:variable name="_constraint" select="Constraint"/>
<xsl:if test="@Action='Drop' or @Action='ReAdd'"><xsl:if test="string-length(../TABLE_REFERENCE/Constraint[text()=$_constraint])=0 or string-length(../TABLE_ORIG_REFERENCE/Constraint[text()=$_constraint])=0">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<xsl:value-of select="$_constraint"/>]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
BEGIN	
-- drop existing References and re-add later to prevent issues with dependant columns
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]  
	DROP CONSTRAINT <xsl:value-of select="$_constraint"/>
END	
GO
</xsl:if></xsl:if>
	</xsl:template>

<!-- template calls template for the re-add References Table statements which will get re-added later -->
	<xsl:template match="DropAdd_References[string-length(Constraint)>0]" mode="Add">
	<xsl:if test="@Action='ReAdd'"><xsl:variable name="_constraint" select="Constraint"/><xsl:if test="string-length(../TABLE_REFERENCE/Constraint[text()=$_constraint])=0 or string-length(../TABLE_ORIG_REFERENCE/Constraint[text()=$_constraint])=0">
IF object_id(N'<xsl:value-of select="FK_Table"/>') IS NOT NULL AND (SELECT COLUMNPROPERTY( object_id(N'<xsl:value-of select="PK_Table"/>'), N'<xsl:value-of select="cRefCol1"/>', 'AllowsNull') ) IS NOT NULL
BEGIN
-- re-add existing References that were previously dropped to prevent issues with dependant columns
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>] ADD 
	CONSTRAINT [<xsl:value-of select="Constraint"/>] FOREIGN KEY 
	(	<xsl:for-each select="descendant::*[contains(local-name(),'cKeyCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	) REFERENCES [<xsl:value-of select="PK_Table_Owner"/>].[<xsl:value-of select="PK_Table"/>] ( <xsl:for-each select="descendant::*[contains(local-name(),'cRefCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	)
END
ELSE
	RAISERROR ('Constraint referenced table: <xsl:value-of select="FK_Table"/>, or the related column: <xsl:value-of select="cRefCol1"/> does not exist', 16, 1)
GO
		</xsl:if></xsl:if>
	</xsl:template>

<!-- template calls template to drop Index Table statements -->
	<xsl:template match="TABLE_ORIG_INDEX[string-length(index_name)>0]">
		<xsl:call-template name="DROP_INDEX">
			<xsl:with-param name="_comment">yes</xsl:with-param>
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
	</xsl:template>

<!-- template calls template to output create index statements -->
	<xsl:template match="TABLE_INDEX[string-length(index_name)>0]" mode="NDX">
		<xsl:call-template name="T_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
	</xsl:template>

<!-- template to output Index Table Constraints statements -->
	<xsl:template  name="PK_INDEX">
		<xsl:param name="_towner"/>
		<xsl:variable name="_tname" select="TABLE_NAME"/>
		<xsl:variable name="_desc" select="index_description"/>
		<xsl:variable name="_name" select="index_name"/>
		<xsl:variable name="_keys" select="index_keys"/>
		<xsl:variable name="_gpname" select="substring-after($_desc,'located on ')"/>
		<xsl:variable name="_select"> (SELECT COLUMNPROPERTY( OBJECT_ID(N'<xsl:value-of select="$_tname"/>'), N'</xsl:variable>
		<xsl:variable name="_end1">', 'AllowsNull')) IS NOT NULL  AND </xsl:variable>
		<xsl:variable name="_end2">', 'AllowsNull')) IS NOT NULL  </xsl:variable>
		<xsl:variable name="_part0">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_keys"/>
				<xsl:with-param name="substringIn">(-)</xsl:with-param>
				<xsl:with-param name="substringOut"> DESC</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part1">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="concat('[',$_part0,']')"/>
				<xsl:with-param name="substringIn">, </xsl:with-param>
				<xsl:with-param name="substringOut">], [</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part2">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part1"/>
				<xsl:with-param name="substringIn">[</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_select"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part3">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part2"/>
				<xsl:with-param name="substringIn">],</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_end1"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part4">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part3"/>
				<xsl:with-param name="substringIn">]</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_end2"/>
			</xsl:call-template> 
		</xsl:variable>
		<xsl:if test="contains($_desc, 'primary key') or contains($_desc, 'unique key')">
IF ( <xsl:value-of select="$_part4"/> ) AND EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND (SELECT FILEGROUP_ID('<xsl:value-of select="$_gpname"/>')) IS NOT NULL
BEGIN
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>] ADD 
	CONSTRAINT [<xsl:value-of select="$_name"/>] <xsl:if test="contains($_desc, 'primary key')">PRIMARY KEY</xsl:if><xsl:if test="contains($_desc, 'unique key')">UNIQUE</xsl:if> <xsl:choose><xsl:when test="contains($_desc, 'nonclustered')"> NONCLUSTERED </xsl:when><xsl:when test="contains($_desc, 'clustered')"> CLUSTERED </xsl:when></xsl:choose>
	(
		<xsl:value-of select="$_part1"/>
	)  ON [<xsl:value-of select="$_gpname"/>] 
END
ELSE
	RAISERROR ('Constraint column(s): <xsl:value-of select="$_keys"/> do(es) not exist, or Table or File Group does not exist.  Check for other errors.', 16, 1)
GO
		</xsl:if>
	</xsl:template>

<!-- template to output create index statements -->
	<xsl:template  name="T_INDEX">
		<xsl:param name="_towner"> </xsl:param>
		<xsl:variable name="_tname" select="TABLE_NAME"/>
		<xsl:variable name="_desc" select="index_description"/>
		<xsl:variable name="_name" select="index_name"/>
		<xsl:variable name="_keys" select="index_keys"/>
		<xsl:variable name="_gpname" select="substring-after($_desc,'located on ')"/>
		<xsl:variable name="_part0">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_keys"/>
				<xsl:with-param name="substringIn">(-)</xsl:with-param>
				<xsl:with-param name="substringOut"> DESC</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part1">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="concat('[',$_part0,']')"/>
				<xsl:with-param name="substringIn">, </xsl:with-param>
				<xsl:with-param name="substringOut">], [</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:if test="not(contains($_desc, 'primary key')) and not(contains($_desc, 'unique key'))">
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND (SELECT FILEGROUP_ID('<xsl:value-of select="$_gpname"/>')) IS NOT NULL
BEGIN		
	CREATE <xsl:if test="contains($_desc, 'nonclustered, unique located')">UNIQUE</xsl:if> INDEX [<xsl:value-of select="$_name"/>] ON [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>](<xsl:value-of select="$_part1"/>) ON [<xsl:value-of select="$_gpname"/>]
END	
ELSE
	RAISERROR ('Table or File Group does not exist.  Check for other errors that caused the table to not be created.', 16, 1)
	
GO
		</xsl:if>
	</xsl:template>

<!-- template to add (CK) checked and default table constraints -->
	<xsl:template match="TABLE_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]">
IF (SELECT OBJECT_ID(N'<xsl:value-of select="CONSTRAINT_NAME"/>')) IS NULL AND EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	ALTER TABLE [<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>] ADD 
	CONSTRAINT  [<xsl:value-of select="CONSTRAINT_NAME"/>] 
	<xsl:if test="substring(CONSTRAINT_TYPE,1,1)='C'"> CHECK </xsl:if><xsl:if test="substring(CONSTRAINT_TYPE,1,1)='D'"> DEFAULT </xsl:if> <xsl:value-of disable-output-escaping="yes" select="CONSTRAINT_CLAUSE"/><xsl:if test="substring(CONSTRAINT_TYPE,1,1)='D' and string-length(COLUMN_NAME)>0"> FOR <xsl:value-of select="COLUMN_NAME"/></xsl:if>
END
ELSE
	PRINT 'Check Constraint <xsl:value-of select="CONSTRAINT_NAME"/> already exist. No need to add.'
GO
	</xsl:template>

<!-- template to drop original (CK) checked and default table constraints -->
	<xsl:template match="TABLE_ORIG_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]">
-- drop original table check constraints
IF (SELECT OBJECT_ID(N'<xsl:value-of select="CONSTRAINT_NAME"/>')) IS NOT NULL AND EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	ALTER TABLE [<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	DROP CONSTRAINT [<xsl:value-of select="CONSTRAINT_NAME"/>]
END
ELSE
	PRINT 'Check Constraint <xsl:value-of select="CONSTRAINT_NAME"/> or the table does not exist, so it cannot be removed'
GO
	</xsl:template>

<!-- template to drop original table indexes - non unique and non pk -->
	<xsl:template name="DROP_INDEX">
		<xsl:param name="_comment"/>
		<xsl:param name="_towner"/>
		<xsl:variable name="_tname" select="TABLE_NAME"/>
		<xsl:variable name="_desc" select="index_description"/>
		<xsl:variable name="_name" select="index_name"/>
		<xsl:variable name="_keys" select="index_keys"/>
		<xsl:if test="not(contains($_desc, 'primary key')) and not(contains($_desc, 'unique key'))">
<xsl:if test="not(contains($_comment, 'no'))">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- drop original non-unique indexes on table </xsl:if>
	DROP INDEX [<xsl:value-of select="$_tname"/>].[<xsl:value-of select="$_name"/>] 
<xsl:if test="not(contains($_comment, 'no')) and string-length($_name)>0">END
GO
</xsl:if>
		</xsl:if>
		<xsl:if test="contains($_desc, 'primary key') or contains($_desc, 'unique key')">
<xsl:if test="not(contains($_comment, 'no'))">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- drop original pk or unique indexes on table </xsl:if>
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>] 
	DROP CONSTRAINT [<xsl:value-of select="$_name"/>]		
<xsl:if test="not(contains($_comment, 'no')) and string-length($_name)>0">END
GO
</xsl:if>
		</xsl:if>
	</xsl:template>

<!-- template to output DROP constraint statements for (FK) referenced tables -->
	<xsl:template match="TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="DROP">
-- drop new FK Constraints on table, these should not exist, but just to be sure...
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<xsl:value-of select="Constraint"/>]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
BEGIN
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]  
	DROP CONSTRAINT <xsl:value-of select="Constraint"/>
END
GO
	</xsl:template>

<!-- template to add original DROP constraint statements for (FK) referenced tables -->
	<xsl:template match="TABLE_ORIG_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]">
		<xsl:variable name="_constraint" select="Constraint"/><xsl:if test="string-length(../TABLE_REFERENCE/Constraint[text()=$_constraint])=0">
-- drop original FK Constraints on table, if they exists
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<xsl:value-of select="Constraint"/>]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
BEGIN	
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]  
	DROP CONSTRAINT <xsl:value-of select="Constraint"/>
END	
GO
		</xsl:if>
	</xsl:template>

<!-- template to output (FK) referenced table constraints -->
	<xsl:template match="TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="ADD">
-- add new FK Constraints to Table
IF object_id(N'<xsl:value-of select="FK_Table"/>') IS NOT NULL AND (SELECT COLUMNPROPERTY( object_id(N'<xsl:value-of select="PK_Table"/>'), N'<xsl:value-of select="cRefCol1"/>', 'AllowsNull') ) IS NOT NULL
BEGIN
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>] ADD 
	CONSTRAINT [<xsl:value-of select="Constraint"/>] FOREIGN KEY 
	(	<xsl:for-each select="descendant::*[contains(local-name(),'cKeyCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	) REFERENCES [<xsl:value-of select="PK_Table_Owner"/>].[<xsl:value-of select="PK_Table"/>] ( <xsl:for-each select="descendant::*[contains(local-name(),'cRefCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	)
END
ELSE
	RAISERROR ('Constraint referenced table: <xsl:value-of select="FK_Table"/>, or the related column: <xsl:value-of select="cRefCol1"/> does not exist', 16, 1)
GO
	</xsl:template>

<!-- template to output View statements -->
	<xsl:template match="VIEW">
		<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="VIEW_NAME"/>]') and OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="VIEW_NAME"/>]
END
GO

			<xsl:for-each select="VIEW_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required view object dependency is missing, unable to create view', 16, 1)
	END

</xsl:if>
			</xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="CREATE_TEXT"/></xsl:call-template>

		</xsl:if>
		<xsl:if test="@Action='Alter'">
			<xsl:text> 
</xsl:text>
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="ALTER_Text"><xsl:with-param name="elem" select="CREATE_TEXT"/><xsl:with-param name="type">VIEW</xsl:with-param></xsl:call-template>
		</xsl:if>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output stored procedure statements -->
	<xsl:template match="SPROC">
		<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="SPROC_NAME"/>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="SPROC_NAME"/>]
END
GO

			<xsl:for-each select="SPROC_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required procedure object dependency is missing, unable to create procedure', 16, 1)
	END

</xsl:if>
			</xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="SPROC_TEXT"/></xsl:call-template>

		</xsl:if>
		<xsl:if test="@Action='Alter'">
			<xsl:text> 
</xsl:text>
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="ALTER_Text"><xsl:with-param name="elem" select="SPROC_TEXT"/><xsl:with-param name="type">PROCEDURE</xsl:with-param></xsl:call-template>
		</xsl:if>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output function statements -->
	<xsl:template match="FUNC">
		<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="FUNC_NAME"/>]') and xtype in (N'FN', N'IF', N'TF'))
BEGIN
	DROP FUNCTION [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="FUNC_NAME"/>]
END
GO

			<xsl:for-each select="FUNC_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required function object dependency is missing, unable to create function', 16, 1)
	END

</xsl:if> 
			</xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="FUNC_TEXT"/></xsl:call-template>

		</xsl:if>
		<xsl:if test="@Action='Alter'">
			<xsl:text> 
</xsl:text>
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="ALTER_Text"><xsl:with-param name="elem" select="FUNC_TEXT"/><xsl:with-param name="type">FUNCTION</xsl:with-param></xsl:call-template>
		</xsl:if>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output Triggers -->
	<xsl:template match="TRIGGER">
		<xsl:if test="@Action='Add'">
if exists (select * from dbo.sysobjects where [name] = '<xsl:value-of select="TRIGGER_NAME"/>' and [type] = 'TR')
BEGIN
	DROP TRIGGER [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="TRIGGER_NAME"/>]
END
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

		<xsl:call-template name="CREATE_Text"><xsl:with-param name="elem" select="TRIGGER_TEXT"/></xsl:call-template>

		</xsl:if>
		<xsl:if test="@Action='Alter'">
			<xsl:text> 
</xsl:text>
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

			<xsl:call-template name="ALTER_Text"><xsl:with-param name="elem" select="TRIGGER_TEXT"/><xsl:with-param name="type">TRIGGER</xsl:with-param></xsl:call-template>
		</xsl:if>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- replace 'create' with 'alter' for the passed in type, VIEW, PROCEDURE, FUNCTION, TRIGGER -->
	<xsl:template name="ALTER_Text">
		<xsl:param name="elem"/>
		<xsl:param name="type"/>
		
<!-- parse all versions of standard case for key words -->
		<xsl:variable name="_lowertype">
			<xsl:call-template name='convertcase'>
				<xsl:with-param name='toconvert' select='$type' />
				<xsl:with-param name='conversion' select="string('lower')" />
			</xsl:call-template>
		</xsl:variable>
		
		<xsl:variable name="_propertype">
			<xsl:call-template name='convertcase'>
				<xsl:with-param name='toconvert' select='$type' />
				<xsl:with-param name='conversion' select="string('proper')" />
			</xsl:call-template>
		</xsl:variable>
		
		<xsl:variable name="_UpperTypeIn"><xsl:value-of select="concat('CREATE ',$type)"/></xsl:variable>
		<xsl:variable name="_UpperTypeOut"><xsl:value-of select="concat('ALTER ',$type)"/></xsl:variable>
		<xsl:variable name="_LowerTypeIn"><xsl:value-of select="concat('create ',$_lowertype)"/></xsl:variable>
		<xsl:variable name="_ProperTypeIn"><xsl:value-of select="concat('Create ',$_propertype)"/></xsl:variable>
		<xsl:variable name="_MixedType1In"><xsl:value-of select="concat('CREATE ',$_lowertype)"/></xsl:variable>
		<xsl:variable name="_MixedType2In"><xsl:value-of select="concat('Create ',$_lowertype)"/></xsl:variable>
		<xsl:variable name="_MixedType3In"><xsl:value-of select="concat('Create ',$type)"/></xsl:variable>
		<xsl:variable name="_MixedType4In"><xsl:value-of select="concat('create ',$type)"/></xsl:variable>
		<xsl:variable name="_MixedType5In"><xsl:value-of select="concat('CREATE ',$_propertype)"/></xsl:variable>
		<xsl:variable name="_MixedType6In"><xsl:value-of select="concat('create ',$_propertype)"/></xsl:variable>
		
		<xsl:for-each select="$elem">
			<xsl:variable name="_firstPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="Text"/>
					<xsl:with-param name="substringIn" select="$_UpperTypeIn"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_secondPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_firstPass"/>
					<xsl:with-param name="substringIn" select="$_LowerTypeIn"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>
			
			<xsl:variable name="_thirdPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_secondPass"/>
					<xsl:with-param name="substringIn" select="$_ProperTypeIn"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_fourthPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_thirdPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType1In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_fifthPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_fourthPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType2In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_sixthPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_fifthPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType3In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_seventhPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_sixthPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType4In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_eighthPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_seventhPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType5In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:variable name="_ninethPass">
				<xsl:call-template name="StartsWithReplace">
					<xsl:with-param name="stringIn" select="$_eighthPass"/>
					<xsl:with-param name="substringIn" select="$_MixedType6In"/>
					<xsl:with-param name="substringOut" select="$_UpperTypeOut"/>
					<xsl:with-param name="once" select="0"/>
				</xsl:call-template>
			</xsl:variable>

			<xsl:call-template name="dblquot-replace">
				<xsl:with-param name="string">
					<xsl:value-of disable-output-escaping="yes"  select="$_ninethPass"/>
				</xsl:with-param>
				<xsl:with-param name="counter" select="0"/>
			</xsl:call-template>
		</xsl:for-each>
		
	</xsl:template>

<!-- output unchanged create text -->
	<xsl:template name="CREATE_Text">
		<xsl:param name="elem"/>
		<xsl:for-each select="$elem">
			<xsl:call-template name="dblquot-replace">
				<xsl:with-param name="string">
					<xsl:value-of disable-output-escaping="yes" select="Text"/>
				</xsl:with-param>
				<xsl:with-param name="counter" select="0"/>
			</xsl:call-template>
		</xsl:for-each>
	</xsl:template>

<!-- replace all occurences of the character 'dblquote'
	by the character '[' and ']' in the string 'string'.-->
	<xsl:template name="dblquot-replace" >
		<xsl:param name="string"/>
		<xsl:param name="counter"/>		
		<xsl:variable name="lt">[</xsl:variable>
		<xsl:variable name="rt">]</xsl:variable>
		<xsl:variable name="dq">"</xsl:variable>
		<xsl:variable name="use">
			<xsl:choose>
				<xsl:when test="($counter mod 2)=0">
					<xsl:value-of select="$lt"/>
				</xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="$rt"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:choose>
			<xsl:when test="contains($string, $dq)">
				<xsl:value-of select="substring-before($string, $dq)"/>
				<xsl:value-of select="$use"/>
				<xsl:call-template name="dblquot-replace">
					<xsl:with-param name="string" select="substring-after($string, $dq)"/>
					<xsl:with-param name="counter" select="$counter + 1"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$string"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

<!-- functional equivalent of substring method -->
	<xsl:template name="SubstringReplace">
		<xsl:param name="stringIn" /> 
		<xsl:param name="substringIn" /> 
		<xsl:param name="substringOut" /> 
		<xsl:choose>
			<xsl:when test="contains($stringIn,$substringIn)">
				<xsl:value-of select="concat(substring-before($stringIn,$substringIn),$substringOut)" /> 
				<xsl:call-template name="SubstringReplace">
					<xsl:with-param name="stringIn" select="substring-after($stringIn,$substringIn)" /> 
					<xsl:with-param name="substringIn" select="$substringIn" /> 
					<xsl:with-param name="substringOut" select="$substringOut" /> 
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$stringIn" /> 
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

	<!-- functional equivalent of starts with / replace substring method -->
	<xsl:template name="StartsWithReplace">
		<xsl:param name="stringIn" /> 
		<xsl:param name="substringIn" /> 
		<xsl:param name="substringOut" />
		<xsl:param name="once" />
		<xsl:variable name="spacedIn" select="$stringIn"/>
		<xsl:choose>
			<xsl:when test="starts-with(normalize-space($spacedIn),$substringIn) and $once=0">
				<xsl:value-of select="concat(substring-before($spacedIn,$substringIn),$substringOut)" /> 
				<xsl:call-template name="SubstringReplace">
					<xsl:with-param name="stringIn" select="substring-after($spacedIn,$substringIn)" /> 
					<xsl:with-param name="substringIn" select="$substringIn" /> 
					<xsl:with-param name="substringOut" select="$substringOut" /> 
					<xsl:with-param name="once" select="$once + 1"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$stringIn" /> 
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

<!-- functional equivalent of change case method -->
<xsl:template name='convertcase'>
	<xsl:param name='toconvert' />
	<xsl:param name='conversion' />
	
	<xsl:variable name="lcletters">abcdefghijklmnopqrstuvwxyz</xsl:variable>
	<xsl:variable name="ucletters">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>

	<xsl:choose>
		<xsl:when test='$conversion="lower"'>
			<xsl:value-of select="translate($toconvert,$ucletters,$lcletters)"/>
		</xsl:when>
		<xsl:when test='$conversion="upper"'>
			<xsl:value-of select="translate($toconvert,$lcletters,$ucletters)"/>
		</xsl:when>
		<xsl:when test='$conversion="proper"'>
			<xsl:call-template name='convertpropercase'>
			<xsl:with-param name='toconvert'>
				<xsl:value-of select="translate($toconvert,$ucletters,$lcletters)"/>
			</xsl:with-param>
			</xsl:call-template>
		</xsl:when>
		<xsl:otherwise>
			<xsl:value-of select='$toconvert' />
		</xsl:otherwise>
	</xsl:choose>
</xsl:template>

<xsl:template name='convertpropercase'>
	<xsl:param name='toconvert' />

	<xsl:if test="string-length($toconvert) > 0">
		<xsl:variable name='f' select='substring($toconvert, 1, 1)' />
		<xsl:variable name='s' select='substring($toconvert, 2)' />
		
		<xsl:call-template name='convertcase'>
			<xsl:with-param name='toconvert' select='$f' />
			<xsl:with-param name='conversion'>upper</xsl:with-param>
		</xsl:call-template>

		<xsl:choose>
			<xsl:when test="contains($s,' ')">
				<xsl:value-of select='substring-before($s," ")'/>
				&#160;
				<xsl:call-template name='convertpropercase'>
				<xsl:with-param name='toconvert' select='substring-after($s," ")' />
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select='$s'/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:if>
</xsl:template>

</xsl:stylesheet>